﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Threading;
using Word = Microsoft.Office.Interop.Word;
using Osobe;
using SkolskaUstanova;
using MolbeIPotvrde;

namespace ElektronskiDnevnik
{
    public partial class PregledUspjehaN : Form
    {
        String JMBG, odabraniUcenik;
        int odabraniPredmet, odabraniRazred, id;
        MySqlConnection conn;
        MySqlDataReader rdr = null;
        List<String> sifre;
        public PregledUspjehaN(String maticni, int predmet, int razred, MySqlConnection c)
        {
            InitializeComponent();
            odabraniPredmet = predmet;
            odabraniRazred = razred;
            conn = c;
            JMBG = maticni;
            sifre = new List<String>();        
        }

        private void Form12_Load(object sender, EventArgs e)
        {
            button6.Enabled = false;
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM Ucenik WHERE Razred = " + odabraniRazred + ";", conn);
            try
            {
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    listBox1.Items.Add(Convert.ToString(rdr["Ime"]) + " " + Convert.ToString(rdr["Prezime"]));
                    sifre.Add(Convert.ToString(rdr["JMBG"]));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            button6.Enabled = true;
            listView1.Items.Clear();
            int indeks = listBox1.SelectedIndex;
            odabraniUcenik = sifre[indeks];
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM Ucenik WHERE JMBG = " + odabraniUcenik + ";", conn);
            try
            {
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    textBox1.Text = Convert.ToString(rdr["Ime"]);
                    textBox2.Text = Convert.ToString(rdr["Prezime"]);
                    textBox3.Text = Convert.ToString(rdr["JMBG"]);
                    pictureBox1.ImageLocation = Convert.ToString(rdr["URLSlike"]);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
             MySqlCommand cmd1 = new MySqlCommand("SELECT * FROM UcenikPredmet WHERE Ucenik = '" + odabraniUcenik + "' AND Predmet = " + odabraniPredmet + ";", conn);
             int ID_up = 0;
            try
             {
                 rdr = cmd1.ExecuteReader();
                 while (rdr.Read())
                     ID_up = Convert.ToInt32(rdr["ID"]);
                 id = ID_up;
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message, "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
             finally
             {
                 if (rdr != null)
                     rdr.Close();
             }
             MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM Ocjene WHERE ID_up =" + ID_up + ";", conn);
             try
             {
                 System.Windows.Forms.ListViewItem item = new System.Windows.Forms.ListViewItem();
                 rdr = cmd2.ExecuteReader();
                 while (rdr.Read())
                 {
                     item = listView1.Items.Add(Convert.ToString((Convert.ToDateTime(rdr["Datum"]).Day + "." + (Convert.ToDateTime(rdr["Datum"])).Month + "." + (Convert.ToDateTime(rdr["Datum"])).Year)));
                     item.SubItems.Add(Convert.ToString(rdr["Ocjena"]));
                     item.SubItems.Add(Convert.ToString(rdr["TipProvjere"]));
                 }
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message, "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
             finally
             {
                 if (rdr != null)
                     rdr.Close();
             }
             MySqlCommand cmd3 = new MySqlCommand("SELECT * FROM ProsjekPoPredmetu WHERE JMBG = '" + odabraniUcenik + "' AND IDPredmeta = " + ID_up + ";", conn);
             try
             {
                 rdr = cmd3.ExecuteReader();
                 while (rdr.Read())
                     textBox4.Text = Convert.ToString(rdr["Prosjek"]);
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message, "Greska", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
             finally
             {
                 if (rdr != null)
                     rdr.Close();
             }

        }

        private void button6_Click(object sender, EventArgs e)
        {
            SaveFileDialog spasiMe = new SaveFileDialog();
            spasiMe.Filter = "pdf files (*.pdf)|*.pdf";
            spasiMe.FilterIndex = 2;
            spasiMe.RestoreDirectory = true;
            if (spasiMe.ShowDialog() == DialogResult.OK)
            {
                String s = spasiMe.FileName;
                Thread myWord = new Thread(delegate()
                {
                    KreirajIzvjestaj(conn, s, id);
                });
                myWord.Start();
            }
        }

        private void KreirajIzvjestaj(MySqlConnection myConnection, string myPath,
            int _up_ID)
        {
            Word._Application myWord = new Word.Application();
            Word._Document myDocument = null;
            myDocument = myWord.Documents.Add();

            String mySelect = "SELECT * FROM Skola;";
            MySqlCommand myCommand = new MySqlCommand(mySelect, conn);
            MySqlDataReader myReader = myCommand.ExecuteReader();

            myReader.Read();

            Word.Paragraph myParagraph;

            myParagraph = myDocument.Content.Paragraphs.Add();
            myParagraph.Range.Text = Convert.ToString(myReader.GetValue(0));
            myParagraph.Range.Font.Bold = 1;
            myParagraph.Format.IndentFirstLineCharWidth(1);
            myParagraph.Format.SpaceAfter = 0;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Content.Paragraphs.Add();
            String mjestoSk = Convert.ToString(myReader.GetValue(2));
            myParagraph.Range.Text = mjestoSk;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Content.Paragraphs.Add();
            myParagraph.Range.Text = Convert.ToString(myReader.GetValue(3));
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Content.Paragraphs.Add();
            myParagraph.Range.Text = Convert.ToString(myReader.GetValue(4)) + " "
                                 + Convert.ToString(myReader.GetValue(5));
            myParagraph.Format.SpaceAfter = 20;
            myParagraph.Range.InsertParagraphAfter();

            myReader.Close();

            mySelect = "SELECT * FROM UcenikPredmetPogled WHERE ID=" +
                Convert.ToString(_up_ID) + ";";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();

            myReader.Read();

            myParagraph = myDocument.Paragraphs.Add();
            String myText = "Ime: ";
            myText = myText + Convert.ToString(myReader["Ime"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Format.SpaceAfter = 0;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Prezime: ";
            myText = myText + Convert.ToString(myReader["Prezime"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Razred: ";
            myText = myText + Convert.ToString(myReader["Razred"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Odjeljenje: ";
            myText = myText + Convert.ToString(myReader["Odjeljenje"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph = myDocument.Paragraphs.Add();
            myText = "Predmet: ";
            myText = myText + Convert.ToString(myReader["Predmet"]);
            myParagraph.Range.Font.Size = 10;
            myParagraph.Range.Font.Bold = 0;
            myParagraph.Range.Text = myText;
            myParagraph.Format.SpaceAfter = 15;
            myParagraph.Range.InsertParagraphAfter();

            myParagraph.Format.SpaceAfter = 0;
            myParagraph.Format.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
            myParagraph.Range.Font.Size = 10;
            myParagraph.Format.SpaceBefore = 0;

            myReader.Close();

            mySelect = "SELECT count(*) FROM Ocjene WHERE ID_up=" +
                Convert.ToString(_up_ID) + ";";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();

            myReader.Read();
            int _brojRedova = Convert.ToInt32(myReader.GetValue(0));

            myReader.Close();

            Word.Table myTable;
            object oEnd = "\\endofdoc";

            Word.Range myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myTable = myDocument.Tables.Add(myWordRange, 1, 3);
            myTable.Range.ParagraphFormat.SpaceAfter = 0;
            myTable.Spacing = 0;
            myTable.Cell(1, 1).Range.Text = "Datum";
            myTable.Cell(1, 2).Range.Text = "Tip provjere";
            myTable.Cell(1, 3).Range.Text = "Ocjena";
            myTable.Range.Rows[1].Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15;

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            Word.Shape myShape;
            myShape = myDocument.Shapes.AddLine(-5, 1, 474, 1, myWordRange);
            myShape.Line.Weight = 1.5F;

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myShape = myDocument.Shapes.AddLine(-5, 4, 474, 4, myWordRange);
            myShape.Line.Weight = 1;

            mySelect = "SELECT * FROM Ocjene WHERE ID_up=" +
                Convert.ToString(_up_ID) + ";";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            if (_brojRedova > 0)
                myTable = myDocument.Tables.Add(myWordRange, _brojRedova, 3);
            myTable.Range.ParagraphFormat.SpaceAfter = 0;
            myTable.Range.Font.Bold = 0;
            for (int i = 1; i <= _brojRedova; i++)
            {
                myReader.Read();
                DateTime d = Convert.ToDateTime(myReader["Datum"]);
                String s = Convert.ToString(d.Day) + "." +
                           Convert.ToString(d.Month) + "." +
                           Convert.ToString(d.Year);
                myTable.Cell(i, 1).Range.Text = s;
                myTable.Cell(i, 2).Range.Text = Convert.ToString(myReader["TipProvjere"]);
                myTable.Cell(i, 3).Range.Text = Convert.ToString(myReader["Ocjena"]);
            }

            myReader.Close();

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myShape = myDocument.Shapes.AddLine(-5, 1, 474, 1, myWordRange);
            myShape.Line.Weight = 1.5F;

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myShape = myDocument.Shapes.AddLine(-5, 4, 474, 4, myWordRange);
            myShape.Line.Weight = 1;

            mySelect = "SELECT Prosjek FROM ProsjekPoPredmetu WHERE IDPredmeta=" +
                Convert.ToString(_up_ID) + ";";
            myCommand = new MySqlCommand(mySelect, conn);
            myReader = myCommand.ExecuteReader();
            myReader.Read();

            myWordRange = myDocument.Bookmarks.get_Item(ref oEnd).Range;
            myTable = myDocument.Tables.Add(myWordRange, 1, 3);
            myTable.Range.ParagraphFormat.SpaceAfter = 0;
            myTable.Spacing = 0;
            myTable.Cell(1, 1).Range.Text = "Prosjek";
            myTable.Cell(1, 2).Range.Text = "";
            myTable.Cell(1, 3).Range.Text = Convert.ToString(myReader["Prosjek"]);
            myTable.Range.Rows[1].Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15;

            myReader.Close();
            String myString = "Sarajevo, " +
                       Convert.ToString(DateTime.Now.Day) + "." +
                       Convert.ToString(DateTime.Now.Month) + "." +
                       Convert.ToString(DateTime.Now.Year) + ".g.";
            myParagraph = myDocument.Paragraphs.Add();
            myParagraph.Format.SpaceAfter = 20;
            myParagraph.Format.SpaceBefore = 20;
            myParagraph.Range.Text = myString;
            myParagraph.Range.InsertParagraphAfter();

            myDocument.SaveAs(myPath, Word.WdSaveFormat.wdFormatPDF);

            myWord.Quit(false);

            System.Diagnostics.Process.Start(@myPath);
        }
    }
}
